﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Microsoft.Win32;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Data;
using System.Reflection;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using System.ComponentModel;


namespace AlgoLogicMessenger
{
    //Interaction logic for ImportData.xaml
    public partial class ImportData : Page
    {
        public ImportData()
        {
            InitializeComponent();
        }

        private void BtnBack_Click(object sender, RoutedEventArgs e)
        {
            NavigationService nav = NavigationService.GetNavigationService(this);
            nav.Navigate(new System.Uri("ManageQuizModule.xaml", UriKind.RelativeOrAbsolute));
        }

        private void btnOpen_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                OpenFileDialog dlg = new OpenFileDialog();
                dlg.DefaultExt = ".xlsx";
                dlg.Filter = "Excel Documents (*.xlsx);(.xls)|*.xlsx;*.xls"; ;

                dlg.Multiselect = true; //select multiple files

                // Get the selected file name and display in a TextBox
                excelBox.Clear();
                if (dlg.ShowDialog() != null)
                {
                    // Open document
                    excelBox.Text = dlg.FileName;
                    this.dgImport.DataContext = dataGet.Table;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public DataView dataGet
        {
            get
            {
                Excel.Application excelApp = new Excel.Application();
                Excel.Workbook workbook;
                Excel.Worksheet worksheet;
                Excel.Range range;

                workbook = excelApp.Workbooks.Open(excelBox.Text);
                worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(2);

                int column = 0;
                int row = 0;

                range = worksheet.UsedRange;
                DataTable dt = new DataTable();

                for (column = 1; column <= range.Columns.Count; column++)
                {
                    dt.Columns.Add((range.Cells[1, column] as Excel.Range).Value2.ToString());
                }

                for (row = 2; row <= range.Rows.Count; row++)
                {
                    DataRow dr = dt.NewRow();
                    for (column = 1; column <= range.Columns.Count; column++)
                    {
                        dr[column - 1] = (range.Cells[row, column] as Excel.Range).Value2.ToString();
                    }
                    dt.Rows.Add(dr);
                    dt.AcceptChanges();
                }

                workbook.Close(true, Missing.Value, Missing.Value);
                excelApp.Quit();
                return dt.DefaultView;
            }
        }

        private void BtnImport_Click(object sender, RoutedEventArgs e)
        {
            if (dgImport.SelectedItems.Count > 0)
            {
                string exists = null;
                for (int j = 0; j < dgImport.SelectedItems.Count; j++)
                {
                    var path = excelBox.Text;
                    FileInfo file = new FileInfo(path);
                    string fn = file.Name;

                    var row = ((System.Data.DataRowView)dgImport.SelectedItems[j]).Row;
                    DataRowView dv = new DataRowView(row);
                    string qns = dv.qns.ToString();

                    var conn = new SqlConnection(@"Data Source=localhost;Initial Catalog=AlgoLogicDB;Integrated Security=True");
                    conn.Open();
                    var comm = new SqlCommand();
                    comm.Connection = conn;
                    comm.CommandText = " if exists(select * from export where Question=@Question) select 'True' as 'Exists' else select 'False' as 'Exists'";
                    comm.Parameters.AddWithValue("@Question", qns);
                    comm.ExecuteNonQuery();
                    SqlDataReader dr = comm.ExecuteReader();
                    if (dr.HasRows == true)
                    {
                        while (dr.Read())
                        {
                            exists = (string)dr["Exists"];
                        }
                        if (exists == "True")
                        {
                            MessageBox.Show("Unable to import existing questions");
                            j = dgImport.SelectedItems.Count;
                            dr.Close();
                            conn.Close();
                        }
                    }
                    dr.Close();
                    conn.Close();
                }

                if (exists == "False")
                {
                    for (int j = 0; j < dgImport.SelectedItems.Count; j++)
                    {
                        var path = excelBox.Text;
                        FileInfo file = new FileInfo(path);
                        string fn = file.Name;

                        var row = ((System.Data.DataRowView)dgImport.SelectedItems[j]).Row;
                        DataRowView dv = new DataRowView(row);
                        string qns = dv.qns.ToString();
                        string answer = dv.answer.ToString();
                        string picpath = dv.picpath.ToString();
                        string a = dv.a.ToString();
                        string b = dv.b.ToString();
                        string c = dv.c.ToString();
                        string d = dv.d.ToString();
                        string m = dv.m.ToString();
                        string s = dv.s.ToString();
                        string cat = dv.cat.ToString();

                        try
                        {
                            var conn = new SqlConnection(@"Data Source=localhost;Initial Catalog=AlgoLogicDB;Integrated Security=True");
                            conn.Open();
                            var comm = new SqlCommand();
                            comm.Connection = conn;
                            comm.CommandText = " insert into Export(Question, Category, FileName, Answer, Image, OptA, OptB, OptC, OptD, Min, Sec) values(@Question, @Category, @FileName, @Answer, @Image, @OptA, @OptB, @OptC, @OptD, @Min, @Sec)";
                            comm.Parameters.AddWithValue("@Question", qns);
                            comm.Parameters.AddWithValue("@Category", cat);
                            comm.Parameters.AddWithValue("@FileName", fn);
                            comm.Parameters.AddWithValue("@Answer", answer);
                            comm.Parameters.AddWithValue("@Image", picpath);
                            comm.Parameters.AddWithValue("@OptA", a);
                            comm.Parameters.AddWithValue("@OptB", b);
                            comm.Parameters.AddWithValue("@OptC", c);
                            comm.Parameters.AddWithValue("@OptD", d);
                            comm.Parameters.AddWithValue("@Min", m);
                            comm.Parameters.AddWithValue("@Sec", s);
                            comm.ExecuteNonQuery();
                            conn.Close();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Unable to establish connection to database");
                        }
                    }
                    MessageBox.Show("Questions are imported Successfully!", "Notification", MessageBoxButton.OK, MessageBoxImage.Information);
                }
            }
            else
            {
                MessageBox.Show("Please Select a Question!", "Notification", MessageBoxButton.OK, MessageBoxImage.Information);
            }
        }

        private void BtnDeleteAll_Click(object sender, RoutedEventArgs e)
        {
            const string message = "Are you sure to delete all existing questions in the database?";
                const string caption = "Deleting question";
                MessageBoxResult result = MessageBox.Show(message, caption, MessageBoxButton.YesNo, MessageBoxImage.Question);

                if (result == MessageBoxResult.Yes)
                {
                    var conn = new SqlConnection(@"Data Source=localhost;Initial Catalog=AlgoLogicDB;Integrated Security=True");
                    var comm = new SqlCommand();
                    comm.Connection = conn;
                    try
                    {
                        conn.Open();
                        comm.CommandText = "delete from export DBCC CHECKIDENT (export, RESEED, 0)";
                        comm.ExecuteNonQuery();
                        conn.Close();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Unable to establish connection to the database");
                    }

                    finally
                    {
                        MessageBox.Show("Deleted all existing questions in the database successfully.");
                    }   
                }
        }

        private void Page_PreviewKeyDown(object sender, KeyEventArgs e)
        {
            if (e.Key == Key.F5 || e.Key == Key.Back)
            {
                e.Handled = true;
            }
        }
    }
}